<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/7
 * Time: 17:32
 */
namespace Admin\Model;
use Think\Model;

class RepayDetailModel extends Model
{
	protected $trueTableName = 'bypal_repay_detail';


	/**
	 * 还款账单明细
	 */
	public function get_repay_detail($borrow_id)
	{
		$select_sql = "select repay_detail_id,binfo_id,period_number,start_date,end_date,principal,interest,poundage,period_recovery,cumulative_recovery
        ,repay_date,repaymtime,isoverdue,demurrage,fine_money,actual_pricipal,actual_interest from bypal_repay_detail where binfo_id='$borrow_id'
        order by period_number ";

		$Model = new \Think\Model();
		$values = $Model->query($select_sql);
		return $values;
	}

    public function borrow_overdue_list($search_array,$page,$rows){
        $time=time();
        $sql="select t1.binfo_id,FROM_UNIXTIME(t1.start_date,'%Y-%m-%d') start_date,FROM_UNIXTIME(t1.end_date,'%Y-%m-%d') end_date,t1.repay_date,
        case when t1.repay_date=0 then '未还款' else FROM_UNIXTIME(t1.repay_date,'%Y-%m-%d') end repay_status,t4.cust_name,t3.fac_simple_name,t5.fund_balance,
        t1.period_number,round((t1.principal+t1.interest+t1.poundage),2) recover_money,t1.demurrage,
        case when t1.repay_date=0 then floor(($time-t1.end_date)/86400) else floor((t1.repay_date-t1.end_date)/86400) end overdue_day
        from bypal_repay_detail t1
        left JOIN bypal_borrow_info t2 ON t1.binfo_id = t2.borrow_id
        left join bypal_facilitator_info t3 on t2.borrow_busid=t3.id
        left join bypal_cust_info t4 on t2.borrow_custid=t4.cust_id
        left join bypal_balance_invest t5 on t2.borrow_custid=t5.cust_id";

        $count_sql="select  count(repay_detail_id) as count  from bypal_repay_detail ";
        $where=" where 1=1 and (repay_date=0 and $time >end_date+86400) or isoverdue=1";
        if($search_array['repay_status']!=''){
            $repay_status=$search_array['repay_status'];
            if($repay_status==1){
                $where=" where 1=1 and repay_date!=0 and isoverdue=1";
            }else if($repay_status==2){
                $where=" where 1=1 and ((repay_date=0 and $time >end_date+86400) or isoverdue=1) and repay_date=0";
            }
        }
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$where." order by t1.end_date desc ".$limit;
        $count_sql.=$where;

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count_values=$Model->query($count_sql);
        $result['total']=$count_values[0]['count'];
        $result['rows']=$values;
        return $result;
    }

    public function borrow_overdue_remark_list($search_array,$page,$rows){
        $time=time();
        $sql="select t1.repay_detail_id,t1.binfo_id,FROM_UNIXTIME(t1.end_date,'%Y-%m-%d') end_date,t1.repay_date,t4.cust_name,t4.cust_phone,t3.fac_simple_name,t1.period_number,
            round((t1.principal+t1.interest+t1.poundage),2) recover_money,t5.remark_content,floor(($time-t1.end_date)/86400) overdue_day,
            case when t5.remark_promisetime!=0 then FROM_UNIXTIME(t5.remark_promisetime,'%Y-%m-%d') else '----' end remark_promisetime,
            case when t5.update_time!=0 then FROM_UNIXTIME(t5.update_time,'%Y-%m-%d') else '----' end update_time
            from bypal_repay_detail t1
            left join bypal_borrow_info t2 on t1.binfo_id = t2.borrow_id
            left join bypal_facilitator_info t3 on t2.borrow_busid=t3.id
            left join bypal_cust_info t4 on t2.borrow_custid=t4.cust_id
            left join bypal_borrow_overdueremark t5 on t1.repay_detail_id=t5.repay_detail_id
            where (repay_date=0 and ".$time." >end_date+86400) ";

        $count_sql="select count(t1.repay_detail_id) count
            from bypal_repay_detail t1
            left join bypal_borrow_overdueremark t5 on t1.repay_detail_id=t5.repay_detail_id
            where (repay_date=0 and ".$time." >end_date+86400) ";

        $where="";
        if($search_array['type']==3){//查询拥有约定还款日期
            $where=" and t5.remark_promisetime!=0";
        }else if($search_array['type']==4){//查询未备注逾期
            $where=" and t5.remark_content is null";
        }
        if($search_array['remark_start_promisetime']!=''&&$search_array['remark_end_promisetime']!=''){
            $start_time=strtotime($search_array['remark_start_promisetime']);
            $end_time=strtotime($search_array['remark_end_promisetime']);
            $where=" and t5.remark_promisetime between $start_time and $end_time";
        }
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$where." order by t1.end_date desc ".$limit;
        $count_sql.=$where;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count_values=$Model->query($count_sql);
        $result['total']=$count_values[0]['count'];
        $result['rows']=$values;
        return $result;
    }

    public function repay_cashback($search_array,$page,$rows){
        $sql="select t1.money,t1.create_time,t2.binfo_id,t2.period_number,t2.period_recovery,round((t2.repay_date-t2.end_date)/86400,2) advance_day,
        t3.buinfo_infotips,t4.cust_name,case when t4.device_type=1 then 'ios' when t4.device_type=2 then 'andriod' else 'web' end device_type
				from bypal_repay_cashback t1
				left join bypal_repay_detail t2 on t1.repay_detail_id=t2.repay_detail_id
				left join bypal_borrow_buinessinfo t3 on t2.binfo_id=t3.binfo_borrowid
				left join bypal_cust_info t4 on t3.buinfo_infotips=t4.cust_id
				where 1=1 ";

        $count_sql = "select count(t1.id)
				from bypal_repay_cashback t1
				left join bypal_repay_detail t2 on t1.repay_detail_id=t2.repay_detail_id
				left join bypal_borrow_buinessinfo t3 on t2.binfo_id=t3.binfo_borrowid
				left join bypal_cust_info t4 on t3.buinfo_infotips=t4.cust_id
				where 1=1 ";

        $sum_sql = "select sum(t1.money) sum_money,sum(t2.period_recovery) sum_period_recovery
				from bypal_repay_cashback t1
				left join bypal_repay_detail t2 on t1.repay_detail_id=t2.repay_detail_id
				left join bypal_borrow_buinessinfo t3 on t2.binfo_id=t3.binfo_borrowid
				left join bypal_cust_info t4 on t3.buinfo_infotips=t4.cust_id
				where 1=1 ";
        $where="";
        if($search_array['select_custName']!=''){
            $where.=" and t4.cust_name like '%".$search_array['select_custName']."%'";
        }
        if($search_array['select_borrowId']!=''){
            $where.=" and t2.binfo_id=".$search_array['select_borrowId'];
        }
        $sql.=$where;
        $count_sql.=$where;
        $sum_sql.=$where;
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$limit;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count_values=$Model->query($count_sql);
        $sum_values=$Model->query($sum_sql);

        $array_sum = array();
        $array_sum[0]['advance_day'] = '还款金额总计:';
        $array_sum[0]['period_recovery'] = $sum_values[0]['sum_period_recovery'] . ' 元';
        $array_sum[0]['money'] = '返现金额总计:';
        $array_sum[0]['create_time'] = $sum_values[0]['sum_money']. ' 元';
        $result['total']=$count_values[0]['count'];
        $result['rows']=$values;
        $result['footer']=$array_sum;
        return $result;
    }

    public function overdueremark_detail($borrow_id){
        $sql="select remark_content,FROM_UNIXTIME(remark_promisetime,'%Y-%m-%d') remark_promisetime,t1.user_id,FROM_UNIXTIME(update_time,'%Y-%m-%d') update_time,
        t2.user_name from bypal_borrow_overdueremark t1
        left join bypal_cust_remarkuser t2 on t1.user_id=t2.user_id where borrow_id='".$borrow_id."' order by update_time desc";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

}